package com.hunter.api.handler;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import com.hunter.api.help.DbHelper;
import com.hunter.api.help.exception.BusinessAssert;
import com.hunter.api.help.exception.BusinessException;
import com.hunter.api.help.result.BusinessCode;
import com.hunter.api.help.result.ErrorHelp;
import com.hunter.api.help.JsonHelper;
import com.hunter.api.mapper.SqlMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * SqlHelper
 *
 * @author Hunter
 * @version 1.0
 * @date 2019/11/13 13:26
 */
@Slf4j
@Service
public class SqlHandler {
    @Autowired
    private SqlMapper sqlMapper;


    public JSONObject queryRow(JSONObject sql_json, JSONObject paramsObj){
        JSONObject json = new JSONObject();
        String sql = sql_json.getString("sql");
        //拼接参数、验证必填参数： sql_json,paramsObj
        sql = prepareSql(sql,sql_json, paramsObj);
        //断言sql不可能为空
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR,String.format(" sql: %s, params: %s",sql,paramsObj));
        json = sqlMapper.queryRow(sql);
        return json;
    }

    public JSONObject queryAll(JSONObject sql_json, JSONObject paramsObj,Integer is_limit){
        JSONObject json = new JSONObject();
        String sql = sql_json.getString("sql");
        //拼接参数、验证必填参数： sql_json,paramsObj
        sql = prepareSql(sql,sql_json, paramsObj);
        sql = StringUtils.replace(sql,";", "");
        //断言sql不能为空
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR,String.format(" sql: %s, params: %s",sql,paramsObj));
        //查询数量
        json.put("count",sqlMapper.queryAllCount(sql));
        if (is_limit==1){
            //如果需要分页需要额外拼接参数
            sql = prepareLvSql(sql,sql_json, paramsObj);
            BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR,String.format(" sql: %s, params: %s",sql,paramsObj));
        }
        json.put("list",sqlMapper.queryAll(sql));
        return json;


    }


    public JSONObject queryScalar(JSONObject sql_json, JSONObject paramsObj){
        JSONObject json = new JSONObject();
        String sql = sql_json.getString("sql");
        //拼接参数、验证必填参数： sql_json,paramsObj
        sql = prepareSql(sql,sql_json, paramsObj);
        //断言sql不能为空
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR,String.format(" sql: %s, params: %s",sql,paramsObj));
        json.put("value",sqlMapper.queryScalar(sql));
        return json;



    }

    public JSONObject execSql(JSONObject sql_json, JSONObject paramsObj,String sqlType){
        JSONObject json = new JSONObject();
        String sql = sql_json.getString("sql");
        //拼接参数、验证必填参数： sql_json,paramsObj
        sql = prepareSql(sql, sql_json, paramsObj);
        //断言sql不可能为空
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR,String.format(" sql: %s, params: %s",sql,paramsObj));
        if ("insert".equalsIgnoreCase(sqlType)) {
            json.put("value", sqlMapper.insertData(sql));
        } else {
            sqlMapper.updateData(sql);
            json.put("value", 1);
        }
        return json;

    }

    /**
     * 执行sql不需要全量输入
     * @param sql_json
     * @param paramsObj
     * @param sqlType
     * @return
     * @throws Exception
     */
    public JSONObject execSql(JSONObject sql_json, JSONObject paramsObj,String sqlType,JSONObject info){
        JSONObject json = new JSONObject();

        String sql = sql_json.getString("sql");
        //拼接参数、验证必填参数： sql_json,paramsObj
        sql = prepareSql(sql, sql_json, paramsObj,info);

        //断言sql不可能为空
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR,String.format(" sql: %s, params: %s",sql,paramsObj));
        if ("insert".equalsIgnoreCase(sqlType)) {
            json.put("value", sqlMapper.insertData(sql));
        } else {
            sqlMapper.updateData(sql);
            json.put("value", 1);
        }

        return json;

    }

    public String prepareLvSql(String sql,JSONObject sql_json, JSONObject paramsObj){
        JSONObject dataObj = new JSONObject();
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR,String.format(" sql: %s, params: %s",sql,paramsObj));

        JSONArray params = JsonHelper.getJSONArray(sql_json,"params", null);
        JSONArray sorters = JsonHelper.getJSONArray(paramsObj,"sorters", null);
        Integer page_index = JsonHelper.getInteger(paramsObj,"page_index", 1);
        Integer page_size = JsonHelper.getInteger(paramsObj,"page_size", 20);

        sql = StringUtils.replace(sql,";", " ");

        //排序
        if (sorters!=null){
            int index=StringUtils.lastIndexOf(sql.toLowerCase()," order by ");
            if (index!= -1){
                sql = StringUtils.substring(sql,0,index);
            }
            for (int i = 0; i < sorters.size(); i++) {
                String field = JsonHelper.getString(sorters.getJSONObject(i),"field","");
                String sort = JsonHelper.getString(sorters.getJSONObject(i),"sort","");
                if (!field.equals("")){
                    if (i<1){
                        sql += " ORDER BY "+ field;
                    }else{
                        sql += " , "+ field;
                    }

                    if (!sort.equals("")){
                        sql += " "+ sort;
                    }
                }

            }
        }

        int index=StringUtils.lastIndexOf(sql.toLowerCase()," limit ");
        if (index!= -1) {
            sql = StringUtils.substring(sql, 0, index);
        }
        //分页
        sql+="  limit "+page_size;
        sql+=" offset "+(page_index-1)*page_size;
        sql+=" ;";

        if (params == null) {
            return sql;
        }

        //数据替换插入
        for (int i = 0; i < params.size(); i++) {
            JSONObject p = params.getJSONObject(i);
            String key = p.getString("key");
            Object nullValue = p.get("null_value");
            Object value = JsonHelper.get(paramsObj, key, null);
            if (p.getIntValue("required") == 1 && (value==null || value.equals(""))) {
                throw new BusinessException("11002",String.format("参数【 %s 】不能为空",key) );
            }
            if (value == null) {
                value = nullValue;
            }
            dataObj.put(key, value);
        }
        return DbHelper.buildSql(sql, dataObj);
    }

    public String prepareSql(String sql,JSONObject sql_json, JSONObject paramsObj){
        JSONObject dataObj = new JSONObject();
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR);
        JSONArray params = JsonHelper.getJSONArray(sql_json,"params", null);
        if (params == null) {
            return sql;
        }
        //数据替换插入
        for (int i = 0; i < params.size(); i++) {
            JSONObject p = params.getJSONObject(i);
            String key = p.getString("key");
            Object nullValue = p.get("null_value");
            Object value = JsonHelper.get(paramsObj, key, null);
            if (p.getIntValue("required") == 1 && (value == null)) {
                throw new BusinessException("11002",String.format("参数【 %s 】不能为空",key) );
            }
            if (value == null) {
                value = nullValue;
            }
            dataObj.put(key, value);
        }


        return DbHelper.buildSql(sql, dataObj);
    }

    /**
     * 拼接，不需要全量输入
     * @param sql
     * @param sql_json
     * @param paramsObj
     * @param info
     * @return
     * @throws Exception
     */
    public String prepareSql(String sql,JSONObject sql_json, JSONObject paramsObj,JSONObject info){
        JSONObject dataObj = new JSONObject();
        BusinessAssert.notNull(sql,BusinessCode.COMFIG_SQL_ERROR);
        JSONArray params = JsonHelper.getJSONArray(sql_json,"params", null);
        if (params == null) {
            return sql;
        }
        //数据替换插入
        for (int i = 0; i < params.size(); i++) {
            JSONObject p = params.getJSONObject(i);
            String key = p.getString("key");
            Object nullValue = p.get("null_value");
            Object value = JsonHelper.get(paramsObj, key, null);
            if (value == null ) {
                value= JsonHelper.get(info, key, null);
            }
            if (value == null) {
                value = nullValue;
            }
            dataObj.put(key, value);
        }


        return DbHelper.buildSql(sql, dataObj);
    }

}
